<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 4.2.1">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">


<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"lianyouli.gitee.io","root":"/","scheme":"Mist","version":"7.8.0","exturl":false,"sidebar":{"position":"right","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":false,"style":null},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":false,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},"path":"search.xml"};
  </script>

  <meta name="description" content="大家对trino没有怎么听说过，我也是在做flink时，有人flink贡献者提到 OLAP 场景下 Flink 与 Trino 确实还存在差距，所以尝试去了解了一下，其与presto SQL是一样的，当然，trino与presto SQL的故事不仅仅如此，请参考Trino | We’re rebranding PrestoSQL as Trino。">
<meta property="og:type" content="article">
<meta property="og:title" content="初探trino">
<meta property="og:url" content="https://lianyouli.gitee.io/uncategorized/it-bigdata-distributed-sql-query-engine-trino/index.html">
<meta property="og:site_name" content="联友的Blog">
<meta property="og:description" content="大家对trino没有怎么听说过，我也是在做flink时，有人flink贡献者提到 OLAP 场景下 Flink 与 Trino 确实还存在差距，所以尝试去了解了一下，其与presto SQL是一样的，当然，trino与presto SQL的故事不仅仅如此，请参考Trino | We’re rebranding PrestoSQL as Trino。">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://lianyouli.gitee.io/uncategorized/it-bigdata-distributed-sql-query-engine-trino/image-20220502153434768.png">
<meta property="og:image" content="https://lianyouli.gitee.io/uncategorized/it-bigdata-distributed-sql-query-engine-trino/image-20220502163335198.png">
<meta property="og:image" content="https://lianyouli.gitee.io/uncategorized/it-bigdata-distributed-sql-query-engine-trino/image-20220502163452543.png">
<meta property="og:image" content="https://lianyouli.gitee.io/uncategorized/it-bigdata-distributed-sql-query-engine-trino/image-20220502165441735.png">
<meta property="og:image" content="https://lianyouli.gitee.io/uncategorized/it-bigdata-distributed-sql-query-engine-trino/image-20220502165706165.png">
<meta property="article:published_time" content="2022-05-02T07:23:46.000Z">
<meta property="article:modified_time" content="2022-05-02T09:02:53.197Z">
<meta property="article:author" content="李联友">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://lianyouli.gitee.io/uncategorized/it-bigdata-distributed-sql-query-engine-trino/image-20220502153434768.png">

<link rel="canonical" href="https://lianyouli.gitee.io/uncategorized/it-bigdata-distributed-sql-query-engine-trino/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>

  <title>初探trino | 联友的Blog</title>
  


  <script>
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "https://hm.baidu.com/hm.js?eb9f10e46b1f09801805fc668f13d477";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>




  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

<link rel="alternate" href="/atom.xml" title="联友的Blog" type="application/atom+xml">
</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">联友的Blog</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
      <p class="site-subtitle" itemprop="description">密涅瓦的猫头鹰在黄昏起飞</p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>

  </li>
        <li class="menu-item menu-item-about">

    <a href="/about/" rel="section"><i class="fa fa-user fa-fw"></i>关于</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签<span class="badge">25</span></a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类<span class="badge">8</span></a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档<span class="badge">31</span></a>

  </li>
  </ul>
</nav>




</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://lianyouli.gitee.io/uncategorized/it-bigdata-distributed-sql-query-engine-trino/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/header.jpg">
      <meta itemprop="name" content="李联友">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="联友的Blog">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          初探trino
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>
              

              <time title="创建时间：2022-05-02 15:23:46 / 修改时间：17:02:53" itemprop="dateCreated datePublished" datetime="2022-05-02T15:23:46+08:00">2022-05-02</time>
            </span>

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <blockquote>
<p>大家对trino没有怎么听说过，我也是在做flink时，有人flink贡献者提到 OLAP 场景下 Flink 与 Trino 确实还存在差距，所以尝试去了解了一下，其与presto SQL是一样的，当然，trino与presto SQL的故事不仅仅如此，请参考<a href="https://trino.io/blog/2020/12/27/announcing-trino.html" target="_blank" rel="noopener">Trino | We’re rebranding PrestoSQL as Trino</a>。</p>
</blockquote>
<a id="more"></a>

<h2 id="官网"><a href="#官网" class="headerlink" title="官网"></a>官网</h2><ul>
<li><a href="https://trino.io/" target="_blank" rel="noopener">trino.io</a></li>
<li><a href="https://github.com/trinodb/trino" target="_blank" rel="noopener">github</a></li>
</ul>
<h2 id="安装"><a href="#安装" class="headerlink" title="安装"></a>安装</h2><h3 id="下载-amp-安装"><a href="#下载-amp-安装" class="headerlink" title="下载&amp;安装"></a>下载&amp;安装</h3><p><a href="https://trino.io/download.html" target="_blank" rel="noopener">Trino | Get started with Trino</a></p>
<ul>
<li><p>Server Package</p>
<ol>
<li><p>解压</p>
<img src="/uncategorized/it-bigdata-distributed-sql-query-engine-trino/image-20220502153434768.png" alt="image-20220502153434768" style="zoom:50%;">
</li>
<li><p>创建data数据目录</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mkdir data</span><br></pre></td></tr></table></figure>
</li>
<li><p>创建etc配置目录</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mkdir etc</span><br></pre></td></tr></table></figure>

</li>
</ol>
<p>其目录结构如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">(base) ➜  trino-server-379 tree -L 1</span><br><span class="line">.</span><br><span class="line">├── NOTICE</span><br><span class="line">├── README.txt</span><br><span class="line">├── bin</span><br><span class="line">├── data</span><br><span class="line">├── etc</span><br><span class="line">├── lib</span><br><span class="line">├── plugin</span><br><span class="line">└── var</span><br></pre></td></tr></table></figure>
</li>
<li><p>Command line client</p>
<p>trino-cli-379-executable.jar</p>
<p>将此文件下载之后，放到Server Package解压之后的bin目录，然后软链接此文件为trino</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">(base) ➜  bin ln -s trino-cli-379-executable.jar trino</span><br><span class="line">(base) ➜  bin ls -l</span><br><span class="line">total 20592</span><br><span class="line">-rwxr-xr-x  1 arthur  staff      1450  4 29 09:36 launcher</span><br><span class="line">-rw-r--r--  1 arthur  staff        65  4 29 09:36 launcher.properties</span><br><span class="line">-rwxr-xr-x  1 arthur  staff     14608  4 29 09:36 launcher.py</span><br><span class="line">drwxr-xr-x  5 arthur  staff       160  4 29 09:36 procname</span><br><span class="line">lrwxr-xr-x  1 arthur  staff        28  5  2 12:39 trino -&gt; trino-cli-379-executable.jar</span><br><span class="line">-rwxr--r--@ 1 arthur  staff  10238827  5  2 03:28 trino-cli-379-executable.jar</span><br></pre></td></tr></table></figure>



</li>
</ul>
<h2 id="trino配置"><a href="#trino配置" class="headerlink" title="trino配置"></a>trino配置</h2><p>示例配置，放到了gitee上<a href="https://gitee.com/lianyouli/trino-quickstart-etc" target="_blank" rel="noopener">trino-quickstart-etc: trino入门配置</a></p>
<p>配置文档，请参考 <a href="https://trino.io/docs/current/installation/deployment.html" target="_blank" rel="noopener">Deploying Trino — Trino 379 Documentation</a></p>
<h3 id="node-properties"><a href="#node-properties" class="headerlink" title="node.properties"></a>node.properties</h3><blockquote>
<p>environment: 你随意，dev/sit/uat/prod等都可以</p>
<p>id: 节点的唯一标识，同一个集群里不能重名就好</p>
<p>data-dir:  请配置成你自己的数据目录，请参考步骤安装中的创建的数据目录</p>
</blockquote>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">node.environment</span>=<span class="string">development</span></span><br><span class="line"><span class="meta">node.id</span>=<span class="string">ffff-ffff-ffff</span></span><br><span class="line"><span class="meta">node.data-dir</span>=<span class="string">/Users/arthur/opt/Software/trino/trino-server-379/data</span></span><br></pre></td></tr></table></figure>

<h3 id="jvm-config"><a href="#jvm-config" class="headerlink" title="jvm.config"></a>jvm.config</h3><blockquote>
<p>官网示例配置，不过我将内存配置从16G –&gt; 4G。</p>
</blockquote>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="attr">-server</span></span><br><span class="line"><span class="attr">-Xmx4G</span></span><br><span class="line"><span class="meta">-XX</span>:<span class="string">-UseBiasedLocking</span></span><br><span class="line"><span class="meta">-XX</span>:<span class="string">+UseG1GC</span></span><br><span class="line"><span class="meta">-XX</span>:<span class="string">G1HeapRegionSize=32M</span></span><br><span class="line"><span class="meta">-XX</span>:<span class="string">+ExplicitGCInvokesConcurrent</span></span><br><span class="line"><span class="meta">-XX</span>:<span class="string">+ExitOnOutOfMemoryError</span></span><br><span class="line"><span class="meta">-XX</span>:<span class="string">+HeapDumpOnOutOfMemoryError</span></span><br><span class="line"><span class="meta">-XX</span>:<span class="string">-OmitStackTraceInFastThrow</span></span><br><span class="line"><span class="meta">-XX</span>:<span class="string">ReservedCodeCacheSize=512M</span></span><br><span class="line"><span class="meta">-XX</span>:<span class="string">PerMethodRecompilationCutoff=10000</span></span><br><span class="line"><span class="meta">-XX</span>:<span class="string">PerBytecodeRecompilationCutoff=10000</span></span><br><span class="line"><span class="meta">-Djdk.attach.allowAttachSelf</span>=<span class="string">true</span></span><br><span class="line"><span class="meta">-Djdk.nio.maxCachedBufferSize</span>=<span class="string">2000000</span></span><br></pre></td></tr></table></figure>



<h3 id="config-properties"><a href="#config-properties" class="headerlink" title="config.properties"></a>config.properties</h3><p>trino是类似MPP的架构，要运行起来需要两个角色： Coordinator 和 Worker</p>
<ul>
<li><p><strong>Coordinator</strong><br>接收客户发起的查询请求和管理查询的执行；</p>
<p>设置参数coordinator为true，官方建议使用专用的一台服务器来做Coordinator</p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="attr">coordinator</span>=<span class="string">true</span></span><br></pre></td></tr></table></figure>

<p>如果想设置Coordinator节点也做为worker，可以设置下面参数node-scheduler.include-coordinator为true</p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">node-scheduler.include-coordinator</span>=<span class="string">true</span></span><br></pre></td></tr></table></figure>



</li>
</ul>
<ul>
<li><p><strong>Worker</strong></p>
<p>运行任务的节点。</p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="attr">coordinator</span>=<span class="string">false</span></span><br></pre></td></tr></table></figure>

</li>
</ul>
<blockquote>
<p>单机配置需要设置coordinator=true和node-scheduler.include-coordinator=true</p>
<p>http.port根据实际情况设置即可</p>
</blockquote>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="attr">coordinator</span>=<span class="string">true</span></span><br><span class="line"><span class="meta">node-scheduler.include-coordinator</span>=<span class="string">true</span></span><br><span class="line"><span class="meta">http-server.http.port</span>=<span class="string">7080</span></span><br><span class="line"><span class="meta">query.max-memory</span>=<span class="string">50GB</span></span><br><span class="line"><span class="meta">query.max-memory-per-node</span>=<span class="string">1GB</span></span><br><span class="line"><span class="meta">discovery.uri</span>=<span class="string">http://localhost:7080</span></span><br></pre></td></tr></table></figure>



<h3 id="log-properties"><a href="#log-properties" class="headerlink" title="log.properties"></a>log.properties</h3><p>日志级别设置</p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">io.trino</span>=<span class="string">INFO</span></span><br></pre></td></tr></table></figure>



<h2 id="Catalog配置"><a href="#Catalog配置" class="headerlink" title="Catalog配置"></a>Catalog配置</h2><p>trino是通过各种connector去访问各种类型的数据源，而这些connector的配置是通过catalog去管理的。</p>
<p>即通过在catalog里配置数据源的连接信息，用户名，密码等信息，将各种数据源连通起来。</p>
<h3 id="层级"><a href="#层级" class="headerlink" title="层级"></a>层级</h3><blockquote>
<p>catalog &gt; schema &gt; table</p>
</blockquote>
<ul>
<li><p>catalog</p>
<p>catalog名称与catalog目录的各个properties的配置文件名称同名</p>
</li>
<li><p>schema</p>
<p>如果没有的话，可能是default或者可以手动配置，例如kafka。</p>
<p>有可能是数据库名称，例如mysql。</p>
</li>
<li><p>table</p>
<p>可能是table名称，例如mysql</p>
<p>也可能是其它的，例如kafka的topic名称。</p>
</li>
</ul>
<h3 id="目录结构"><a href="#目录结构" class="headerlink" title="目录结构"></a>目录结构</h3><blockquote>
<p> etc &gt; catalog</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">(base) ➜  etc git:(master) ✗ tree -L 2</span><br><span class="line">.</span><br><span class="line">├── README.MD</span><br><span class="line">├── catalog</span><br><span class="line">│   ├── jmx.properties</span><br><span class="line">│   ├── kafka_flink_sensor_iot.properties</span><br><span class="line">│   ├── mysql_tests.properties</span><br><span class="line">│   └── tpch.properties</span><br><span class="line">├── config.properties</span><br><span class="line">├── jvm.config</span><br><span class="line">├── kafka</span><br><span class="line">│   └── lab-flink-sensor-iot.json</span><br><span class="line">├── log.properties</span><br><span class="line">└── node.properties</span><br><span class="line"></span><br><span class="line">2 directories, 10 files</span><br></pre></td></tr></table></figure>



<h3 id="配置示例"><a href="#配置示例" class="headerlink" title="配置示例"></a>配置示例</h3><h4 id="jmx"><a href="#jmx" class="headerlink" title="jmx"></a>jmx</h4><p><a href="https://trino.io/docs/current/connector/jmx.html" target="_blank" rel="noopener">JMX connector — Trino 379 Documentation</a></p>
<p><strong>jmx.properties</strong></p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">connector.name</span>=<span class="string">jmx</span></span><br></pre></td></tr></table></figure>

<h4 id="tpch"><a href="#tpch" class="headerlink" title="tpch"></a>tpch</h4><p><a href="https://trino.io/docs/current/connector/tpch.html" target="_blank" rel="noopener">TPCH connector — Trino 379 Documentation</a></p>
<p><strong>tpch.properties</strong></p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">connector.name</span>=<span class="string">jmx</span></span><br></pre></td></tr></table></figure>

<h4 id="mysql"><a href="#mysql" class="headerlink" title="mysql"></a>mysql</h4><p><a href="https://trino.io/docs/current/connector/mysql.html" target="_blank" rel="noopener">MySQL connector — Trino 379 Documentation</a></p>
<p><strong>mysql_tests.properties</strong></p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">connector.name</span>=<span class="string">mysql</span></span><br><span class="line"><span class="meta">connection-url</span>=<span class="string">jdbc:mysql://localhost:3306</span></span><br><span class="line"><span class="meta">connection-user</span>=<span class="string">nifi</span></span><br><span class="line"><span class="meta">connection-password</span>=<span class="string">Awesome@123</span></span><br></pre></td></tr></table></figure>

<h4 id="kafka"><a href="#kafka" class="headerlink" title="kafka"></a>kafka</h4><p><a href="https://trino.io/docs/current/connector/kafka.html" target="_blank" rel="noopener">Kafka connector — Trino 379 Documentation</a></p>
<p><strong>kafka_flink_sensor_iot.properties</strong></p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">connector.name</span>=<span class="string">kafka</span></span><br><span class="line"><span class="meta">kafka.table-names</span>=<span class="string">lab-flink-sensor-iot</span></span><br><span class="line"><span class="meta">kafka.nodes</span>=<span class="string">localhost:9092</span></span><br><span class="line"><span class="meta">kafka.hide-internal-columns</span>=<span class="string">false</span></span><br></pre></td></tr></table></figure>



<h3 id="Decoder"><a href="#Decoder" class="headerlink" title="Decoder"></a>Decoder</h3><p><strong>Decoder</strong>是将数据从原始数据格式解析成我们方便读取的格式。</p>
<p>为什么需要呢decoder呢，请看下图，kafka的消息是csv格式的，所以我们在查看或者查询时都不方便。</p>
<p><img src="/uncategorized/it-bigdata-distributed-sql-query-engine-trino/image-20220502163335198.png" alt="image-20220502163335198"></p>
<p>下图是配置了Decoder之后效果，可以看到，_message里的数据展平成独立字段了。</p>
<p><img src="/uncategorized/it-bigdata-distributed-sql-query-engine-trino/image-20220502163452543.png" alt="image-20220502163452543"></p>
<h4 id="配置"><a href="#配置" class="headerlink" title="配置"></a>配置</h4><ul>
<li><p>路径</p>
<p>以kafka配置为例，其目录结构</p>
<blockquote>
<p>etc &gt; kafka </p>
</blockquote>
</li>
<li><p>文件名称随意</p>
</li>
</ul>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">(base) ➜  etc git:(master) ✗ tree -L 2</span><br><span class="line">.</span><br><span class="line">....</span><br><span class="line">├── kafka</span><br><span class="line">│   └── lab-flink-sensor-iot.json</span><br><span class="line">....</span><br></pre></td></tr></table></figure>

<ul>
<li><p>文件内容</p>
<blockquote>
<p>需要与你的数据匹配上</p>
<p>tableName: 表名</p>
<p>schemaName: schema名称，kafka默认是default</p>
<p>topicName: kafka的topic名称</p>
</blockquote>
<figure class="highlight json"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line">&#123;</span><br><span class="line">    <span class="attr">"tableName"</span>: <span class="string">"lab-flink-sensor-iot"</span>,</span><br><span class="line">    <span class="attr">"schemaName"</span>: <span class="string">"default"</span>,</span><br><span class="line">    <span class="attr">"topicName"</span>: <span class="string">"lab-flink-sensor-iot"</span>,</span><br><span class="line">    <span class="attr">"message"</span>: &#123;</span><br><span class="line">      <span class="attr">"dataFormat"</span>: <span class="string">"csv"</span>,</span><br><span class="line">      <span class="attr">"fields"</span>: [</span><br><span class="line">        &#123;</span><br><span class="line">          <span class="attr">"name"</span>: <span class="string">"id"</span>,</span><br><span class="line">          <span class="attr">"type"</span>: <span class="string">"VARCHAR(10)"</span>,</span><br><span class="line">          <span class="attr">"mapping"</span>: <span class="string">"0"</span></span><br><span class="line">        &#125;,</span><br><span class="line">        &#123;</span><br><span class="line">          <span class="attr">"name"</span>: <span class="string">"timestamp"</span>,</span><br><span class="line">          <span class="attr">"type"</span>: <span class="string">"BIGINT"</span>,</span><br><span class="line">          <span class="attr">"mapping"</span>: <span class="string">"1"</span></span><br><span class="line">        &#125;,</span><br><span class="line">        &#123;</span><br><span class="line">          <span class="attr">"name"</span>: <span class="string">"temperature"</span>,</span><br><span class="line">          <span class="attr">"type"</span>: <span class="string">"DOUBLE"</span>,</span><br><span class="line">          <span class="attr">"mapping"</span>: <span class="string">"2"</span></span><br><span class="line">        &#125;</span><br><span class="line">      ]</span><br><span class="line">    &#125;</span><br><span class="line">  &#125;</span><br></pre></td></tr></table></figure>

</li>
</ul>
<h3 id="Encoder"><a href="#Encoder" class="headerlink" title="Encoder"></a>Encoder</h3><p><strong>Encoder</strong>是将方便我们读取的数据序列化成方便机器存储的格式。同Decoder，配置也是共享的。</p>
<h2 id="trino客户端"><a href="#trino客户端" class="headerlink" title="trino客户端"></a>trino客户端</h2><h3 id="帮助"><a href="#帮助" class="headerlink" title="帮助"></a>帮助</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br></pre></td><td class="code"><pre><span class="line">(base) ➜  trino-server-379 bin/trino --<span class="built_in">help</span></span><br><span class="line">Trino <span class="built_in">command</span> line interface</span><br><span class="line"></span><br><span class="line">USAGE:</span><br><span class="line"></span><br><span class="line">trino [-h] [--debug] [--<span class="built_in">disable</span>-compression] [--external-authentication] [--ignore-errors] [--insecure]</span><br><span class="line">      [--krb5-disable-remote-service-hostname-canonicalization] [--password] [--[no-]progress]</span><br><span class="line">      [--use-system-truststore] [--version] [--access-token=&lt;token&gt;] [--catalog=&lt;catalog&gt;] [--client-info=&lt;info&gt;]</span><br><span class="line">      [--client-request-timeout=&lt;timeout&gt;] [--client-tags=&lt;tags&gt;] [--editing-mode=&lt;editing-mode&gt;] [--execute=&lt;execute&gt;]</span><br><span class="line">      [-f=&lt;file&gt;] [--http-proxy=&lt;proxy&gt;] [--keystore-password=&lt;password&gt;] [--keystore-path=&lt;path&gt;]</span><br><span class="line">      [--keystore-type=&lt;<span class="built_in">type</span>&gt;] [--krb5-config-path=&lt;path&gt;] [--krb5-credential-cache-path=&lt;path&gt;]</span><br><span class="line">      [--krb5-keytab-path=&lt;path&gt;] [--krb5-principal=&lt;principal&gt;] [--krb5-remote-service-name=&lt;name&gt;]</span><br><span class="line">      [--krb5-service-principal-pattern=&lt;pattern&gt;] [--network-logging=&lt;level&gt;] [--output-format=&lt;format&gt;]</span><br><span class="line">      [--schema=&lt;schema&gt;] [--server=&lt;server&gt;] [--session-user=&lt;user&gt;] [--socks-proxy=&lt;proxy&gt;] [--<span class="built_in">source</span>=&lt;<span class="built_in">source</span>&gt;]</span><br><span class="line">      [--timezone=&lt;timezone&gt;] [--trace-token=&lt;token&gt;] [--truststore-password=&lt;password&gt;] [--truststore-path=&lt;path&gt;]</span><br><span class="line">      [--truststore-type=&lt;<span class="built_in">type</span>&gt;] [--user=&lt;user&gt;]</span><br><span class="line">      [--external-authentication-redirect-handler=&lt;externalAuthenticationRedirectHandler&gt;]...</span><br><span class="line">      [--extra-credential=&lt;credential&gt;]... [--resource-estimate=&lt;estimate&gt;]... [--session=&lt;session&gt;]...</span><br><span class="line"></span><br><span class="line">OPTIONS:</span><br><span class="line">      --access-token=&lt;token&gt; Access token</span><br><span class="line">      --catalog=&lt;catalog&gt;    Default catalog</span><br><span class="line">      --client-info=&lt;info&gt;   Extra information about client making query</span><br><span class="line">      --client-request-timeout=&lt;timeout&gt;</span><br><span class="line">                             Client request timeout (default: 2m)</span><br><span class="line">      --client-tags=&lt;tags&gt;   Client tags</span><br><span class="line">      --debug                Enable debug information</span><br><span class="line">      --<span class="built_in">disable</span>-compression  Disable compression of query results</span><br><span class="line">      --editing-mode=&lt;editing-mode&gt;</span><br><span class="line">                             Editing mode [EMACS, VI] (default: EMACS)</span><br><span class="line">      --execute=&lt;execute&gt;    Execute specified statements and <span class="built_in">exit</span></span><br><span class="line">      --external-authentication</span><br><span class="line">                             Enable external authentication</span><br><span class="line">      --external-authentication-redirect-handler=&lt;externalAuthenticationRedirectHandler&gt;</span><br><span class="line">                             External authentication redirect handlers: DESKTOP_OPEN, SYSTEM_OPEN, PRINT, OPEN, ALL</span><br><span class="line">                               (default: ALL)</span><br><span class="line">      --extra-credential=&lt;credential&gt;</span><br><span class="line">                             Extra credentials (property can be used multiple <span class="built_in">times</span>; format is key=value)</span><br><span class="line">  -f, --file=&lt;file&gt;          Execute statements from file and <span class="built_in">exit</span></span><br><span class="line">  -h, --<span class="built_in">help</span>                 Show this <span class="built_in">help</span> message and <span class="built_in">exit</span></span><br><span class="line">      --http-proxy=&lt;proxy&gt;   HTTP proxy to use <span class="keyword">for</span> server connections</span><br><span class="line">      --ignore-errors        Continue processing <span class="keyword">in</span> batch mode when an error occurs (default is to <span class="built_in">exit</span> immediately)</span><br><span class="line">      --insecure             Skip validation of HTTP server certificates (should only be used <span class="keyword">for</span> debugging)</span><br><span class="line">      --keystore-password=&lt;password&gt;</span><br><span class="line">                             Keystore password</span><br><span class="line">      --keystore-path=&lt;path&gt; Keystore path</span><br><span class="line">      --keystore-type=&lt;<span class="built_in">type</span>&gt; Keystore <span class="built_in">type</span></span><br><span class="line">      --krb5-config-path=&lt;path&gt;</span><br><span class="line">                             Kerberos config file path (default: /etc/krb5.conf)</span><br><span class="line">      --krb5-credential-cache-path=&lt;path&gt;</span><br><span class="line">                             Kerberos credential cache path</span><br><span class="line">      --krb5-disable-remote-service-hostname-canonicalization</span><br><span class="line">                             Disable service hostname canonicalization using the DNS reverse lookup</span><br><span class="line">      --krb5-keytab-path=&lt;path&gt;</span><br><span class="line">                             Kerberos key table path (default: /etc/krb5.keytab)</span><br><span class="line">      --krb5-principal=&lt;principal&gt;</span><br><span class="line">                             Kerberos principal to be used</span><br><span class="line">      --krb5-remote-service-name=&lt;name&gt;</span><br><span class="line">                             Remote peer<span class="string">'s kerberos service name</span></span><br><span class="line"><span class="string">      --krb5-service-principal-pattern=&lt;pattern&gt;</span></span><br><span class="line"><span class="string">                             Remote kerberos service principal pattern (default: $&#123;SERVICE&#125;@$&#123;HOST&#125;)</span></span><br><span class="line"><span class="string">      --network-logging=&lt;level&gt;</span></span><br><span class="line"><span class="string">                             Network logging level [NONE, BASIC, HEADERS, BODY] (default: NONE)</span></span><br><span class="line"><span class="string">      --output-format=&lt;format&gt;</span></span><br><span class="line"><span class="string">                             Output format for batch mode [ALIGNED, VERTICAL, TSV, TSV_HEADER, CSV, CSV_HEADER,</span></span><br><span class="line"><span class="string">                               CSV_UNQUOTED, CSV_HEADER_UNQUOTED, JSON, NULL] (default: CSV)</span></span><br><span class="line"><span class="string">      --password             Prompt for password</span></span><br><span class="line"><span class="string">      --[no-]progress        Show query progress</span></span><br><span class="line"><span class="string">      --resource-estimate=&lt;estimate&gt;</span></span><br><span class="line"><span class="string">                             Resource estimate (property can be used multiple times; format is key=value)</span></span><br><span class="line"><span class="string">      --schema=&lt;schema&gt;      Default schema</span></span><br><span class="line"><span class="string">      --server=&lt;server&gt;      Trino server location (default: localhost:8080)</span></span><br><span class="line"><span class="string">      --session=&lt;session&gt;    Session property (property can be used multiple times; format is key=value; use '</span>SHOW</span><br><span class="line">                               SESSION<span class="string">' to see available properties)</span></span><br><span class="line"><span class="string">      --session-user=&lt;user&gt;  Username to impersonate</span></span><br><span class="line"><span class="string">      --socks-proxy=&lt;proxy&gt;  SOCKS proxy to use for server connections</span></span><br><span class="line"><span class="string">      --source=&lt;source&gt;      Name of source making query (default: trino-cli)</span></span><br><span class="line"><span class="string">      --timezone=&lt;timezone&gt;  Session time zone (default: Asia/Shanghai)</span></span><br><span class="line"><span class="string">      --trace-token=&lt;token&gt;  Trace token</span></span><br><span class="line"><span class="string">      --truststore-password=&lt;password&gt;</span></span><br><span class="line"><span class="string">                             Truststore password</span></span><br><span class="line"><span class="string">      --truststore-path=&lt;path&gt;</span></span><br><span class="line"><span class="string">                             Truststore path</span></span><br><span class="line"><span class="string">      --truststore-type=&lt;type&gt;</span></span><br><span class="line"><span class="string">                             Truststore type</span></span><br><span class="line"><span class="string">      --use-system-truststore</span></span><br><span class="line"><span class="string">                             Use default system (OS) truststore</span></span><br><span class="line"><span class="string">      --user=&lt;user&gt;          Username (default: arthur)</span></span><br><span class="line"><span class="string">      --version              Print version information and exit</span></span><br></pre></td></tr></table></figure>

<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">bin/trino --server localhost:7080</span><br></pre></td></tr></table></figure>

<h3 id="查询元数据"><a href="#查询元数据" class="headerlink" title="查询元数据"></a>查询元数据</h3><ul>
<li><p>查询catalogs</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">trino&gt; show catalogs;</span><br><span class="line">        Catalog         </span><br><span class="line"><span class="comment">------------------------</span></span><br><span class="line"> jmx                    </span><br><span class="line"> kafka_flink_sensor_iot </span><br><span class="line"> mysql_tests            </span><br><span class="line"> system                 </span><br><span class="line"> tpch                   </span><br><span class="line">(5 rows)</span><br><span class="line"></span><br><span class="line">Query 20220502_084453_00010_g7j4t, FINISHED, 1 node</span><br><span class="line">Splits: 7 total, 7 done (100.00%)</span><br><span class="line">0.04 [0 rows, 0B] [0 rows/s, 0B/s]</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询schemas</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line">trino&gt; show schemas from mysql_tests;</span><br><span class="line">       Schema       </span><br><span class="line"><span class="comment">--------------------</span></span><br><span class="line"> datasets           </span><br><span class="line"> hive3              </span><br><span class="line"> hive3statsstore    </span><br><span class="line"> information_schema </span><br><span class="line"> performance_schema </span><br><span class="line"> tests              </span><br><span class="line">(6 rows)</span><br><span class="line"></span><br><span class="line">Query 20220502_084534_00011_g7j4t, FINISHED, 1 node</span><br><span class="line">Splits: 7 total, 7 done (100.00%)</span><br><span class="line">0.05 [6 rows, 99B] [122 rows/s, 1.97KB/s]</span><br><span class="line"></span><br><span class="line">trino&gt; show schemas from kafka_flink_sensor_iot;</span><br><span class="line">       Schema       </span><br><span class="line"><span class="comment">--------------------</span></span><br><span class="line"> default            </span><br><span class="line"> information_schema </span><br><span class="line">(2 rows)</span><br><span class="line"></span><br><span class="line">Query 20220502_084548_00012_g7j4t, FINISHED, 1 node</span><br><span class="line">Splits: 7 total, 7 done (100.00%)</span><br><span class="line">0.03 [2 rows, 35B] [71 rows/s, 1.22KB/s]</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询tables</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">trino&gt; show tables from mysql_tests.tests;</span><br><span class="line">  Table   </span><br><span class="line"><span class="comment">----------</span></span><br><span class="line"> nifi_lab </span><br><span class="line"> sensor   </span><br><span class="line"> sensor2  </span><br><span class="line">(3 rows)</span><br><span class="line"></span><br><span class="line">Query 20220502_084652_00014_g7j4t, FINISHED, 1 node</span><br><span class="line">Splits: 7 total, 7 done (100.00%)</span><br><span class="line">0.11 [3 rows, 66B] [27 rows/s, 600B/s]</span><br><span class="line"></span><br><span class="line">trino&gt; show tables from kafka_flink_sensor_iot.default;</span><br><span class="line">        Table         </span><br><span class="line"><span class="comment">----------------------</span></span><br><span class="line"> lab-flink-sensor-iot </span><br><span class="line">(1 row)</span><br><span class="line"></span><br><span class="line">Query 20220502_084636_00013_g7j4t, FINISHED, 1 node</span><br><span class="line">Splits: 7 total, 7 done (100.00%)</span><br><span class="line">0.07 [1 rows, 37B] [15 rows/s, 569B/s]</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询表结构</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br></pre></td><td class="code"><pre><span class="line">trino&gt; desc mysql_tests.tests.sensor;</span><br><span class="line">   Column    |      Type      | Extra | <span class="keyword">Comment</span> </span><br><span class="line"><span class="comment">-------------+----------------+-------+---------</span></span><br><span class="line"> <span class="keyword">id</span>          | <span class="built_in">varchar</span>(<span class="number">10</span>)    |       |         </span><br><span class="line"> ts          | <span class="built_in">bigint</span>         |       |         </span><br><span class="line"> temperature | <span class="built_in">decimal</span>(<span class="number">18</span>,<span class="number">12</span>) |       |         </span><br><span class="line">(<span class="number">3</span> <span class="keyword">rows</span>)</span><br><span class="line"></span><br><span class="line"><span class="keyword">Query</span> <span class="number">20220502</span>_084733_00015_g7j4t, FINISHED, <span class="number">1</span> node</span><br><span class="line">Splits: <span class="number">7</span> total, <span class="number">7</span> done (<span class="number">100.00</span>%)</span><br><span class="line"><span class="number">0.19</span> [<span class="number">3</span> <span class="keyword">rows</span>, <span class="number">176</span>B] [<span class="number">15</span> <span class="keyword">rows</span>/s, <span class="number">907</span>B/s]</span><br><span class="line"></span><br><span class="line">trino&gt; <span class="keyword">desc</span> kafka_flink_sensor_iot.default.<span class="string">"lab-flink-sensor-iot"</span>;</span><br><span class="line">      Column       |              Type              | Extra |                   <span class="keyword">Comment</span>                   </span><br><span class="line"><span class="comment">-------------------+--------------------------------+-------+---------------------------------------------</span></span><br><span class="line"> <span class="keyword">id</span>                | <span class="built_in">varchar</span>(<span class="number">10</span>)                    |       |                                             </span><br><span class="line"> <span class="built_in">timestamp</span>         | <span class="built_in">bigint</span>                         |       |                                             </span><br><span class="line"> temperature       | <span class="keyword">double</span>                         |       |                                             </span><br><span class="line"> _partition_id     | <span class="built_in">bigint</span>                         |       | <span class="keyword">Partition</span> <span class="keyword">Id</span>                                </span><br><span class="line"> _partition_offset | <span class="built_in">bigint</span>                         |       | <span class="keyword">Offset</span> <span class="keyword">for</span> the message <span class="keyword">within</span> the <span class="keyword">partition</span> </span><br><span class="line"> _message_corrupt  | <span class="built_in">boolean</span>                        |       | Message <span class="keyword">data</span> <span class="keyword">is</span> corrupt                     </span><br><span class="line"> _message          | <span class="built_in">varchar</span>                        |       | Message <span class="built_in">text</span>                                </span><br><span class="line"> _headers          | <span class="keyword">map</span>(<span class="built_in">varchar</span>, <span class="built_in">array</span>(varbinary)) |       | Headers <span class="keyword">of</span> the message <span class="keyword">as</span> <span class="keyword">map</span>               </span><br><span class="line"> _message_length   | <span class="built_in">bigint</span>                         |       | Total <span class="built_in">number</span> <span class="keyword">of</span> message <span class="keyword">bytes</span>               </span><br><span class="line"> _key_corrupt      | <span class="built_in">boolean</span>                        |       | <span class="keyword">Key</span> <span class="keyword">data</span> <span class="keyword">is</span> corrupt                         </span><br><span class="line"> _key              | <span class="built_in">varchar</span>                        |       | <span class="keyword">Key</span> <span class="built_in">text</span>                                    </span><br><span class="line"> _key_length       | <span class="built_in">bigint</span>                         |       | Total <span class="built_in">number</span> <span class="keyword">of</span> <span class="keyword">key</span> <span class="keyword">bytes</span>                   </span><br><span class="line"> _timestamp        | <span class="built_in">timestamp</span>(<span class="number">3</span>)                   |       | Message <span class="built_in">timestamp</span>                           </span><br><span class="line">(<span class="number">13</span> <span class="keyword">rows</span>)</span><br><span class="line"></span><br><span class="line"><span class="keyword">Query</span> <span class="number">20220502</span>_084818_00016_g7j4t, FINISHED, <span class="number">1</span> node</span><br><span class="line">Splits: <span class="number">7</span> total, <span class="number">7</span> done (<span class="number">100.00</span>%)</span><br><span class="line"><span class="number">0.09</span> [<span class="number">13</span> <span class="keyword">rows</span>, <span class="number">1.24</span>KB] [<span class="number">146</span> <span class="keyword">rows</span>/s, <span class="number">13.9</span>KB/s]</span><br></pre></td></tr></table></figure>
</li>
<li><p>Use schema</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">trino&gt; use kafka_flink_sensor_iot.default;</span><br><span class="line"><span class="keyword">USE</span></span><br><span class="line">trino:<span class="keyword">default</span>&gt; <span class="keyword">use</span> mysql_tests.tests;</span><br><span class="line"><span class="keyword">USE</span></span><br></pre></td></tr></table></figure>

</li>
</ul>
<h3 id="查询数据"><a href="#查询数据" class="headerlink" title="查询数据"></a>查询数据</h3><ol>
<li><p>查询mysql</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">trino:tests&gt; select * from  mysql_tests.tests.sensor limit 5;</span><br><span class="line">    id    |      ts       |   temperature    </span><br><span class="line"><span class="comment">----------+---------------+------------------</span></span><br><span class="line"> sensor_1 | 1650585135196 | 102.768561119156 </span><br><span class="line"> sensor_2 | 1650585135196 |  34.569160670600 </span><br><span class="line"> sensor_3 | 1650585135196 |  37.874465883075 </span><br><span class="line"> sensor_4 | 1650585135196 |  18.399362581982 </span><br><span class="line"> sensor_5 | 1650585135196 |  38.842860145725 </span><br><span class="line">(5 rows)</span><br><span class="line"></span><br><span class="line">Query 20220502_085154_00023_g7j4t, FINISHED, 1 node</span><br><span class="line">Splits: 5 total, 5 done (100.00%)</span><br><span class="line">0.49 [5 rows, 0B] [10 rows/s, 0B/s]</span><br></pre></td></tr></table></figure>



</li>
</ol>
<ol start="2">
<li><p>查询kafka</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">trino:tests&gt; select * from kafka_flink_sensor_iot.default."lab-flink-sensor-iot" limit 5;</span><br><span class="line">    id    |   timestamp   |    temperature     | _partition_id | _partition_offset | _message_corrupt |                &gt;</span><br><span class="line"><span class="comment">----------+---------------+--------------------+---------------+-------------------+------------------+----------------&gt;</span></span><br><span class="line"> sensor_5 | 1650585131665 | 37.792825657543474 |             0 |                 0 | false            | sensor_5,165058&gt;</span><br><span class="line"> sensor_1 | 1650585133183 |  99.28945242318458 |             0 |                 1 | false            | sensor_1,165058&gt;</span><br><span class="line"> sensor_1 | 1650595135196 |              -15.0 |             0 |                 2 | false            | sensor_1,165059&gt;</span><br><span class="line"> sensor_3 | 1650585134190 | 36.959661044177736 |             0 |                 3 | false            | sensor_3,165058&gt;</span><br><span class="line"> sensor_1 | 1650585134693 | 100.58058605543529 |             0 |                 4 | false            | sensor_1,165058&gt;</span><br><span class="line">(5 rows)</span><br></pre></td></tr></table></figure>



</li>
</ol>
<ol start="3">
<li><p>联合查询mysql&amp;kafka</p>
<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">trino:tests&gt; select * from mysql_tests.tests.sensor a inner join kafka_flink_sensor_iot.default."lab-flink-sensor-iot" b on a.id = b.id ;</span><br></pre></td></tr></table></figure>

<p><img src="/uncategorized/it-bigdata-distributed-sql-query-engine-trino/image-20220502165441735.png" alt="image-20220502165441735"></p>
</li>
</ol>
<h2 id="UI"><a href="#UI" class="headerlink" title="UI"></a>UI</h2><p><img src="/uncategorized/it-bigdata-distributed-sql-query-engine-trino/image-20220502165706165.png" alt="image-20220502165706165"></p>
<blockquote>
<p>使用tpch的数据可以看到统计变化，数据量小时，基本上没有变化。</p>
</blockquote>

    </div>

    
    
    
        

  <div class="followme">
    <p>欢迎关注我的其它发布渠道</p>

    <div class="social-list">

        <div class="social-item">
          <a target="_blank" class="social-link" href="/atom.xml">
            <span class="icon">
              <i class="fa fa-rss"></i>
            </span>

            <span class="label">RSS</span>
          </a>
        </div>
    </div>
  </div>


      <footer class="post-footer">

        


        
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/IT/it-bigdata-flink-cdc-guava18-blink-conflicts/" rel="prev" title="flink 1.14.4使用flink mysql cdc connector 2.2时报guava18、guava30和blink冲突问题">
      <i class="fa fa-chevron-left"></i> flink 1.14.4使用flink mysql cdc connector 2.2时报guava18、guava30和blink冲突问题
    </a></div>
      <div class="post-nav-item">
    <a href="/IT/it-bigdata-hive-installation/" rel="next" title="hive3 安装">
      hive3 安装 <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#官网"><span class="nav-number">1.</span> <span class="nav-text">官网</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#安装"><span class="nav-number">2.</span> <span class="nav-text">安装</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#下载-amp-安装"><span class="nav-number">2.1.</span> <span class="nav-text">下载&amp;安装</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#trino配置"><span class="nav-number">3.</span> <span class="nav-text">trino配置</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#node-properties"><span class="nav-number">3.1.</span> <span class="nav-text">node.properties</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#jvm-config"><span class="nav-number">3.2.</span> <span class="nav-text">jvm.config</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#config-properties"><span class="nav-number">3.3.</span> <span class="nav-text">config.properties</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#log-properties"><span class="nav-number">3.4.</span> <span class="nav-text">log.properties</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Catalog配置"><span class="nav-number">4.</span> <span class="nav-text">Catalog配置</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#层级"><span class="nav-number">4.1.</span> <span class="nav-text">层级</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#目录结构"><span class="nav-number">4.2.</span> <span class="nav-text">目录结构</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#配置示例"><span class="nav-number">4.3.</span> <span class="nav-text">配置示例</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#jmx"><span class="nav-number">4.3.1.</span> <span class="nav-text">jmx</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#tpch"><span class="nav-number">4.3.2.</span> <span class="nav-text">tpch</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#mysql"><span class="nav-number">4.3.3.</span> <span class="nav-text">mysql</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#kafka"><span class="nav-number">4.3.4.</span> <span class="nav-text">kafka</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Decoder"><span class="nav-number">4.4.</span> <span class="nav-text">Decoder</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#配置"><span class="nav-number">4.4.1.</span> <span class="nav-text">配置</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Encoder"><span class="nav-number">4.5.</span> <span class="nav-text">Encoder</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#trino客户端"><span class="nav-number">5.</span> <span class="nav-text">trino客户端</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#帮助"><span class="nav-number">5.1.</span> <span class="nav-text">帮助</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查询元数据"><span class="nav-number">5.2.</span> <span class="nav-text">查询元数据</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查询数据"><span class="nav-number">5.3.</span> <span class="nav-text">查询数据</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#UI"><span class="nav-number">6.</span> <span class="nav-text">UI</span></a></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt="李联友"
      src="/images/header.jpg">
  <p class="site-author-name" itemprop="name">李联友</p>
  <div class="site-description" itemprop="description"></div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives/">
        
          <span class="site-state-item-count">31</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/categories/">
          
        <span class="site-state-item-count">8</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/tags/">
          
        <span class="site-state-item-count">25</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>
  <div class="links-of-author motion-element">
      <span class="links-of-author-item">
        <a href="https://github.com/lianyouli" title="GitHub → https:&#x2F;&#x2F;github.com&#x2F;lianyouli" rel="noopener" target="_blank"><i class="fab fa-github fa-fw"></i>GitHub</a>
      </span>
  </div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 
  <span itemprop="copyrightYear">2023</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">李联友</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://mist.theme-next.org/" class="theme-link" rel="noopener" target="_blank">NexT.Mist</a> 强力驱动
  </div>

<script src="/js/jquery.min.js"> </script>

        








      </div>
    </footer>
  </div>

  
  <script src="/lib/anime.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>

<script src="/js/utils.js"></script>

<script src="/js/motion.js"></script>


<script src="/js/schemes/muse.js"></script>


<script src="/js/next-boot.js"></script>



<script src="/js/code-unfold.js"></script>


  















  

  

</body>
</html>
